package com.gack.business.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.gack.business.model.StoreSupportFacity;
import com.gack.business.model.StoreSupportService;

/**
 * 
* @ClassName: StoreFacityAndServiceDao 
* @Description: TODO(这里用一句话描述这个类的作用) 
* @author (ZhangXinYu)  
* @date 2018年6月5日 下午4:08:47 
* @version V1.0
 */
@Service
public class StoreFacityAndServiceDao {
	@Autowired
	EntityManager entityManager;
	
	//查询指定门店所有配套设施
	public List<String> getAllStoreFacityids(String storeid){
		List<String> facityids = null;
		String jpql = "select f.facityid from StoreAndFacity f where f.storeid = :storeid";
		Query query = entityManager.createQuery(jpql);
		query.setParameter("storeid", storeid);
		facityids = query.getResultList();
		return facityids;
	}
	
	//查询指定门店所有配套服务
	public List<String> getAllStoreServiceids(String storeid){
		List<String> serviceids = null;
		String jpql = "select s.serviceid from StoreAndService s where s.storeid = :storeid";
		Query query = entityManager.createQuery(jpql);
		query.setParameter("storeid", storeid);
		serviceids = query.getResultList();
		return serviceids;
	}
	
	//查询所有配套设施
	public List<StoreSupportFacity> getAllStoreFacities(String storeid){
		List<StoreSupportFacity> facityids = null;
		String jpql = "select f from StoreSupportFacity f where f.id in (select s.facityid from StoreAndFacity s where s.storeid = :storeid)";
		Query query = entityManager.createQuery(jpql);
		query.setParameter("storeid", storeid);
		facityids = query.getResultList();
		return facityids;
	}
	
	//查询所有配套服务
	public List<StoreSupportService> getAllStoreServices(String storeid){
		List<StoreSupportService> serviceids = null;
		String jpql = "select s from StoreSupportService s where s.id in (select ss.serviceid from StoreAndService ss where ss.storeid = :storeid)";
		Query query = entityManager.createQuery(jpql);
		query.setParameter("storeid", storeid);
		serviceids = query.getResultList();
		return serviceids;
	}
	
	//根据id查询门店配套设施名称拼接字符串
	public String getFacityNamesByIds(List<String> ids){
		String result = null;
		if(ids != null && ids.size() > 0){
			String jpql = "select GROUP_CONCAT(f.name) AS facities from store_support_facity f where f.id in (:ids)";
			StringBuffer idss = new StringBuffer(ids.get(0));
			for(int i = 1; i < ids.size(); i++){
				idss.append(",");
				idss.append(ids.get(i));
			}
			Query query = entityManager.createNativeQuery(jpql);
			query.setParameter("ids", ids);
			result = (String)query.getSingleResult();
		}
		return result;
	}
	
	//根据id查询门店配套服务名称拼接字符串
	public String getServiceNamesByIds(List<String> ids){
		String result = null;
		if(ids != null && ids.size() > 0){
			String jpql = "select GROUP_CONCAT(s.name) AS services from store_support_service s where s.id in (:ids)";
			StringBuffer idss = new StringBuffer(ids.get(0));
			for(int i = 1; i < ids.size(); i++){
				idss.append(",");
				idss.append(ids.get(i));
			}
			Query query = entityManager.createNativeQuery(jpql);
			query.setParameter("ids", ids);
			result = (String)query.getSingleResult();
		}
		return result;
	}
	
	//更新门店配套设施使用状态
	@Transactional
	public void updateStoreSupportFacityIsUse(){
		String jpql_1 = "update store_support_facity set is_use = 0 where is_use <> 2";
		String jpql_2 = "update store_support_facity set is_use = 1 where id in (select DISTINCT saf.facityid from store_and_facity saf "
				+ " LEFT JOIN stores s on s.id = saf.storeid where s.is_use <> 1)";
		Query query = null;
		query = entityManager.createNativeQuery(jpql_1);
		query.executeUpdate();
		query = entityManager.createNativeQuery(jpql_2);
		query.executeUpdate();
	}
	
	//更新门店配套服务使用状态
	@Transactional
	public void updateStoreSupportServiceIsUse(){
		String jpql_1 = "update store_support_service set is_use = 0 where is_use <> 2";
		String jpql_2 = "update store_support_service set is_use = 1 where id in (select DISTINCT sas.serviceid from store_and_service sas"
				+ " LEFT JOIN stores s on s.id = sas.storeid where s.is_use <> 1)";
		Query query = null;
		query = entityManager.createNativeQuery(jpql_1);
		query.executeUpdate();
		query = entityManager.createNativeQuery(jpql_2);
		query.executeUpdate();
	}
	
	//删除门店时清除门店对应的配套设置  配套服务关系  更新  设施与服务的使用状态
	@Transactional
	public void dealStoreSupportFacityAndServiceRelation(String storeid){
		String jpql_1 = "delete from store_and_facity where storeid = :storeid";
		String jpql_2 = "delete from store_and_service where storeid = :storeid";
		Query query = null;
		query = entityManager.createNativeQuery(jpql_1);
		query.setParameter("storeid", storeid);
		query.executeUpdate();
		query = entityManager.createNativeQuery(jpql_2);
		query.setParameter("storeid", storeid);
		query.executeUpdate();
	}
}
